<?php

namespace App\Http\Controllers\Api;

use App;
use App\Util\Common;
use App\Util\GetMac;
use App\Util\SqlServerUtil;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use function Symfony\Component\Debug\header;
use Illuminate\Support\Facades\Storage;
use Excel;

class CBController extends CommonController
{
   
	/**
	* 文件用途描述
	* @date: 2023年2月15日 下午5:19:12
	* @author: juyi
	*/
	public function getCBData(Request $request){
		$FRepKey = $request ->FRepKey ;
		$fbrand = $request ->fbrand??'WINGD' ;
		$fdanweibumen = $request ->fdanweibumen ;
		$keyword =   $request ->keyword ;
		$fmodel =   $request ->fmodel ;
		$all = $request ->all??0 ; // 0 - 默认 不管  ,1-- 采购成本 ，2 -- 加账成本 ，不要FRepKey
		$keywordcgStr =" 2=2 ";
		
		$keywordStr = " 2=2 ";
		$keywordStr0 = "";
		if ($request->has('keyword') and $request->keyword != '' and !empty($request->keyword)) {
			$keyword =  $request->keyword ;
			$keywordStr =" 2=2 and (  charindex(''{$keyword}'', u1.fprodno )>0  ) ";
			$keywordStr =   iconv('UTF-8', 'GBK', $keywordStr); 
			
			$keywordStr0 =" and (  charindex('{$keyword}', u1.fprodno )>0  ) ";
			$keywordStr0 =   iconv('UTF-8', 'GBK', $keywordStr0); 
		}
		
		$fmodelStr = "";
		$fmodelStr1 = "";
		if ($request->has('fmodel') and $request->fmodel != '' and !empty($request->fmodel)) {
			$fmodelStr ="  and  u1.fprodno ='{$fmodel}'  ";
			$fmodelStr1="  and  u1.fprodno =''{$fmodel}''  ";
		}
		
		if($all>=1){
			if($all == 1){
				//1-- 采购成本 ，2 -- 加账成本 
				$cgsql ="set nocount on
				if object_id('tempdb..#tmpprodnoCG') is not null drop table #tmpprodnoCG
				create table #tmpprodnoCG(
				fid int identity(1,1),
				fprodno varchar(200)
				)
				insert into #tmpprodnoCG(fprodno)
				select fprodno from gz_Ycaigoucbf u1 where 1=1 and u1.fbrand='{$fbrand}' {$fmodelStr} {$keywordStr0}   group by fprodno
				declare @sqlmax varchar(max),@filvalue varchar(200)
				declare @i int ,@rwcount int
				set @i=1
				select @rwcount=count(1) from #tmpprodnoCG
				set @sqlmax='select u1.fcode as ''代码'',min(v1.fname) as ''名称'''
				while @i<=@rwcount
				begin
				select @filvalue=fprodno from #tmpprodnoCG where fid=@i
				set @sqlmax=@sqlmax+',sum(case when u1.fprodno='''+@filvalue+''' then isnull(fallAmount,0) else 0 end) as ['+ @filvalue +']'
				set @i=@i+1
				end
				set @sqlmax=@sqlmax+ ' from gz_Ycaigoucbf  u1 inner join gz_YcaigoucbfSet v1 on u1.fcode=v1.fcode and u1.fbrand=v1.fbrand
				where 2=2 and u1.fbrand=''{$fbrand}''  {$fmodelStr1} group by u1.fcode '
				exec( @sqlmax)";
				
				$cgsql = str_replace('2=2',$keywordStr ,$cgsql );
				
				$data = SqlServerUtil::execTosqlYes($this->SqlServer, $cgsql );
				if($data){
					foreach ($data as $k => &$v)
					{
						foreach ($v as $k1=>$v1)
						{
							if( $v[$k1] ==='.00' || $v[$k1] ==='.000' || $v[$k1] ==='.0000' || ($v[$k1]>0 && $v[$k1]<1) ){
								$v[$k1] = floatval($v1);
							}
						}
					}
				}
				showMsg(0,'成功' ,$data );
				
			}
			if($all == 2){
				//2 -- 加账成本 
				$sql = " set nocount on
				if object_id('tempdb..#tmpprodno') is not null drop table #tmpprodno
				create table #tmpprodno(
				fid int identity(1,1),
				fprodno varchar(200)
				)
				insert into #tmpprodno(fprodno)
				select fprodno from gz_Yjiazhangcbf u1 where 1=1 and u1.fbrand='{$fbrand}' {$fmodelStr} {$keywordStr0}  group by fprodno
				declare @sqlmax varchar(max),@filvalue varchar(200)
				declare @i int ,@rwcount int
				set @i=1
				select @rwcount=count(1) from #tmpprodno
				set @sqlmax='select u1.fcode as ''代码'',min(v1.fname) as ''名称'''
				while @i<=@rwcount
				begin
				select @filvalue=fprodno from #tmpprodno where fid=@i
				set @sqlmax=@sqlmax+',sum(case when u1.fprodno='''+@filvalue+''' then fallAmount else 0 end) as ['+ @filvalue +']'
				set @i=@i+1
				end
				set @sqlmax=@sqlmax+ ' from gz_Yjiazhangcbf  u1 inner join gz_YjiazhangcbfSet v1 on u1.fcode=v1.fcode and u1.fbrand=v1.fbrand
				where 2=2 and u1.fbrand=''{$fbrand}''  {$fmodelStr1} group by u1.fcode '
				exec( @sqlmax) " ;
				// 		echo $sql ;die;
				$sql = str_replace('2=2',$keywordStr ,$sql );
				
				
				$data = SqlServerUtil::execTosqlYes($this->SqlServer, $sql );
				if($data){
					foreach ($data as $k => &$v)
					{
						foreach ($v as $k1=>$v1)
						{
							if( $v[$k1] ==='.00' || $v[$k1] ==='.000' || $v[$k1] ==='.0000' || ($v[$k1]>0 && $v[$k1]<1)){
								$v[$k1] = floatval($v1); 
							}
						}
					}
				}
				showMsg(0,'成功' ,$data );
			}
			
			
			
		}
		
		//  26022--采购成本MAN   , 26009 --采购成本WINGD
		if(in_array($FRepKey, [ '26009', '26022'   ])  ){
			if ($request->has('keywordcg') and $request->keywordcg != '' and !empty($request->keywordcg)) {
				$keywordcg =  $request->keywordcg ;
				$keywordcgStr =" 2=2 and (  charindex(''{$keywordcg}'',  v1.fname )>0  ) ";
				$keywordcgStr =   iconv('UTF-8', 'GBK', $keywordcgStr);
			}
			
			$cgsql ="set nocount on
			if object_id('tempdb..#tmpprodnoCG') is not null drop table #tmpprodnoCG
			create table #tmpprodnoCG(
			fid int identity(1,1),
			fprodno varchar(200)
			)
			insert into #tmpprodnoCG(fprodno)
			select fprodno from gz_Ycaigoucbf u1 where 1=1 and u1.fbrand='{$fbrand}' {$fmodelStr} {$keywordStr0}  group by fprodno
			declare @sqlmax varchar(max),@filvalue varchar(200)
			declare @i int ,@rwcount int
			set @i=1
			select @rwcount=count(1) from #tmpprodnoCG
			set @sqlmax='select u1.fcode as ''代码'',min(v1.fname) as ''名称'''
			while @i<=@rwcount
			begin
			select @filvalue=fprodno from #tmpprodnoCG where fid=@i
			set @sqlmax=@sqlmax+',sum(case when u1.fprodno='''+@filvalue+''' then isnull(fallAmount,0) else 0 end) as ['+ @filvalue +']'
			set @i=@i+1
			end
			set @sqlmax=@sqlmax+ ' from gz_Ycaigoucbf  u1 inner join gz_YcaigoucbfSet v1 on u1.fcode=v1.fcode and u1.fbrand=v1.fbrand 
			where 2=2 and u1.fbrand=''{$fbrand}''  {$fmodelStr1}  group by u1.fcode '
			exec( @sqlmax)";
			$cgsql = str_replace('2=2',$keywordStr ,$cgsql ); 
			$cgsql = str_replace('2=2',$keywordcgStr ,$cgsql ); 
			
// 			echo '<pre>' ;
// 			echo $cgsql ;die;
			$data = SqlServerUtil::execTosqlYes($this->SqlServer, $cgsql );
			if($data){
				foreach ($data as $k => &$v)
				{
					foreach ($v as $k1=>$v1)
					{
						if( $v[$k1] ==='.00' || $v[$k1] ==='.000' || $v[$k1] ==='.0000'  || ($v[$k1]>0 && $v[$k1]<1) ){
							$v[$k1] = floatval($v1);
						}
					}
				}
			}
			showMsg(0,'成功' ,$data );
			
		}
		
		$fdanweibumenStr= "";
		$fbmjscgStr = "";
		// 加账
		if(in_array($FRepKey, [ '26005', '26023',  '26025',  ])  ){
			if( in_array($FRepKey, [ '26005',    ]) ) {
				//加账清单加账清单(采购)
				// -- 1=1  可以替换成 u1.fbrand='WINGD'
				// -- 2=2  可以替换成 u1.fbrand= ''WINGD''
				$fdanweibumenStr= " 2=2 and charindex(''采购'',v1.fdanweibumen)>0  " ;
				$fdanweibumenStr =   iconv('UTF-8', 'GBK', $fdanweibumenStr);
				
				$fbmjscgStr = " and charindex('采购',v1.fdanweibumen)>0  " ;
				$fbmjscgStr =   iconv('UTF-8', 'GBK', $fbmjscgStr);
			}
			
			if( in_array( $FRepKey, [ '26023',    ]) ) {
				//加账清单(技术)
				$fdanweibumenStr= " 2=2 and charindex(''技术'',v1.fdanweibumen)>0  " ;
				$fdanweibumenStr =   iconv('UTF-8', 'GBK', $fdanweibumenStr);
				
				$fbmjscgStr = " and charindex('技术',v1.fdanweibumen)>0  " ;
				$fbmjscgStr =   iconv('UTF-8', 'GBK', $fbmjscgStr);
			}
			
			if( in_array( $FRepKey, [ '26025',    ]) ) {
				//加账清单(技术)
				$bumenName ="售后";
				$bumenName1 ="生产";
				$bumenName2 ="质量";
				$fdanweibumenStr= " 2=2 and (charindex(''售后'',v1.fdanweibumen)>0  or charindex(''生产'',v1.fdanweibumen)>0 or charindex(''质量'',v1.fdanweibumen)>0 ) " ;
				$fdanweibumenStr =   iconv('UTF-8', 'GBK', $fdanweibumenStr);
				
				$fbmjscgStr = " and ( charindex('售后',v1.fdanweibumen)>0 or charindex('生产',v1.fdanweibumen)>0 or charindex('质量',v1.fdanweibumen)>0 )   " ;
				$fbmjscgStr =   iconv('UTF-8', 'GBK', $fbmjscgStr);
			}
			$sql = " set nocount on
			if object_id('tempdb..#tmpprodno') is not null drop table #tmpprodno
			create table #tmpprodno(
			fid int identity(1,1),
			fprodno varchar(200)
			)
			insert into #tmpprodno(fprodno)
			select u1.fprodno from gz_YjiazhangcbfMX  u1  inner join gz_YjiazhangcbfSet v1 on v1.fcode= u1.fcode 
			where  v1.fbrand='{$fbrand}' {$fmodelStr}  {$fbmjscgStr} {$keywordStr0} group by u1.fprodno
			declare @sqlmax varchar(max),@filvalue varchar(200) 
			declare @i int ,@rwcount int
			set @i=1
			select @rwcount=count(1) from #tmpprodno
			set @sqlmax='select u1.fcode as ''代码'', min(v1.fname) as ''名称'', min(v1.fzjqty) as ''单位'''
					while @i<=@rwcount
					begin
					select @filvalue=fprodno from #tmpprodno where fid=@i
					set @sqlmax=@sqlmax+',sum(case when u1.fprodno='''+@filvalue+''' then fallAmount else 0 end) as ['+ @filvalue +']'
							set @i=@i+1
							end
							set @sqlmax=@sqlmax+ ' from gz_YjiazhangcbfMX  u1 inner join gz_YjiazhangcbfSet v1 on u1.fcode=v1.fcode
			where 2=2 and v1.fbrand=''{$fbrand}''  {$fmodelStr1}  group by u1.fcode '
					exec( @sqlmax) ";
// 				echo '<pre>'	;
// 			echo $sql ;die;
			$sql = str_replace('2=2',$fdanweibumenStr ,$sql );
			$sql = str_replace('2=2',$keywordStr ,$sql );
			
// 			echo $sql ;die;
			$data = SqlServerUtil::execTosqlYes($this->SqlServer, $sql );
// 			dd($data);
			if($data){
				foreach ($data as $k => &$v)
				{
					foreach ($v as $k1=>$v1)
					{
						if( $v[$k1] ==='.00' || $v[$k1] ==='.000' || $v[$k1] ==='.0000' || ($v[$k1]>0 && $v[$k1]<1)  ){
							$v[$k1] = floatval($v1);
						}
					}
				}
			}
			showMsg(0,'成功' ,$data );
		}
    	
    }
    
    
    
    
	

}
